﻿Public Class BudgetsForm

    Private Sub BudgetsForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadPeriodBox()
        '     LoadBudgetsGrid()
        Dim clm As DataGridViewColumn
        Dim i As Integer = 0

        For Each clm In BudgetsGrid.Columns
            i = i + clm.Width
        Next
        SplitContainer1.SplitterDistance = i
        LoadAllocationsBox()
        LoadRegionBox()

    End Sub

    

    Private Sub LoadPaymentsGrid()
        Dim TheView As DataView
        Dim sql As String
        Dim TheDate As DateTime
        Dim r As Integer
        Job("Loading Payments...")
        ShowLoadingForm()

        Try
            sql = "select distinct month from patterns p, pattern_ids ids " & _
            "where p.pattern_ID = ids.Pattern_ID " & _
              " and ids.Period_ID = " & PeriodBox.SelectedValue & _
              " and ids.Allocation_ID = '" & AllocationBox.SelectedValue & "'"
        Catch ex As Exception
            PaymentGrid.DataSource = Nothing
            HideLoadingForm()
            Job("Ready")
            Exit Sub
            'period box is not ready yet
        End Try

        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            PaymentGrid.DataSource = Nothing
            HideLoadingForm()
            Exit Sub
        End If

        Try
            sql = "select sum(Month_Total) as Allocation, 0 as Change, p.RID, UPIN, Provider_Name as Provider, a.LA_Code, LA_Name as [Local Authority], a.Region_Code, "
            For r = 0 To TheView.Count - 1
                TheDate = TheView(r).Item("Month")
                sql = sql + "sum(case Month when " & TheDate.ToOADate - 2 & " then Month_Total else 0 end) as [" & MonthName(TheDate.Month) & " " & TheDate.Year & "],"
            Next

            'remove the last ,
            sql = Mid(sql, 1, Len(sql) - 1)

            sql = sql & " from PaymentprofilesV2 p, " & _
                        " all_provider_fields a " & _
                        " where p.Allocation_ID = '" & AllocationBox.SelectedValue & "'" & _
                        " and a.Region_Code = '" & RegionBox.SelectedValue & "'" & _
                        " and p.RID = a.RID " & _
                        " and p.Period_ID = " & PeriodBox.SelectedValue & _
                        " group by p.RID, UPIN, Provider_Name, a.LA_Code, LA_Name, a.Region_Code "
        Catch ex As Exception
            PaymentGrid.DataSource = Nothing
            HideLoadingForm()
            Job("Ready")
            Exit Sub
            'other boxes are not ready yet
        End Try


        TheView = LoadDataView(sql)

        PaymentGrid.DataSource = TheView
        PaymentGrid.Columns(0).Frozen = True
        PaymentGrid.Columns(1).Frozen = True
        PaymentGrid.Columns(2).Frozen = True
        PaymentGrid.Columns(3).Frozen = True
        PaymentGrid.Columns(4).Frozen = True


        With PaymentGrid
            If .ColumnCount <> 0 Then
                .Columns("Allocation").Visible = False
                .Columns("RID").Visible = False
                .Columns("LA_Code").Visible = False
                .Columns("Region_Code").Visible = False
                .Columns("Change").Visible = False
                .Columns("UPIN").ReadOnly = True
                .Columns("Provider").ReadOnly = True
                .Columns("Local Authority").ReadOnly = True

            End If
        End With

        Job("Ready")
        HideLoadingForm()

    End Sub

    Private Sub LoadProviderPatternGrid()
        Job("Loading Provider Patterns...")
        Dim sql As String
        Dim TheView As DataView
        Dim TheDate As DateTime
        Dim r As Integer

        ProviderPatternGrid.DataSource = Nothing

        Try
            sql = "select distinct Month " & _
              "from Provider_patterns p " & _
              "where  " & _
              " p.Period_ID = " & PeriodBox.SelectedValue & _
              " and p.Allocation_ID = '" & AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' order by Month"
        Catch ex As Exception
            Job("Ready")
            Exit Sub
            'period box is not ready yet
        End Try


        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            ProviderPatternGrid.DataSource = Nothing
            Job("Ready")
            Exit Sub
        End If


        sql = "select p.RID, UPIN, Provider_Name as Provider, a.LA_Code, LA_Name as [Local Authority], a.Region_Code, "
        For r = 0 To TheView.Count - 1
            TheDate = TheView(r).Item("Month")
            sql = sql + "sum(case Month when " & TheDate.ToOADate - 2 & " then cast(Percentage * 100 as decimal(9,6)) else 0 end) as [" & MonthName(TheDate.Month) & " " & TheDate.Year & "],"
        Next

        'remove the last ,
        sql = Mid(sql, 1, Len(sql) - 1)

        sql = sql & " from provider_Patterns p, " & _
                    " all_provider_fields a " & _
                    " where p.Allocation_ID = '" & AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & _
                    "' and p.RID = a.RID " & _
                    " and p.Period_ID = " & PeriodBox.SelectedValue & _
                    " group by p.RID, UPIN, Provider_Name, a.LA_Code, LA_Name, a.Region_Code "


        TheView = LoadDataView(sql)

        ProviderPatternGrid.DataSource = TheView

        ProviderPatternGrid.Refresh()

        'setup the headers
        With ProviderPatternGrid
            If .Columns.Count <> 0 Then
                .Columns("RID").Visible = False
                .Columns("LA_Code").Visible = False
                .Columns("Region_Code").Visible = False
                .Columns("RID").Frozen = True
                .Columns("UPIN").Frozen = True
                .Columns("Provider").Frozen = True
                .CurrentCell = ProviderPatternGrid.Item(1, 0)
            End If
        End With
        Job("Ready")

    End Sub

    Private Sub LoadRegionBox()
        Job("Loading Regions...")
        RegionBox.DataSource = LoadDataView("Select * from Regions")
        RegionBox.DisplayMember = "Region_Name"
        RegionBox.ValueMember = "Region_Code"
        RegionBox.Refresh()
    End Sub



    Private Sub LoadAllocationsBox()
        Job("Loading Allocations...")
        Dim sql As String
        Dim TheView As DataView

        sql = "Select Allocation_ID as 'ID', Allocation_Description as 'Description', Allocation_Lines.Field_Code, Field_description as 'Field' from Allocation_Lines " & _
              "left outer join Fields_List_Part on Allocation_Lines.Field_Code = Fields_List_Part.Field_Code " & _
              "where Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "'"

        TheView = LoadDataView(sql)

        AllocationBox.DataSource = TheView
        AllocationBox.DisplayMember = "Description"
        AllocationBox.ValueMember = "ID"
        Job("Ready")
    End Sub

    Private Sub LoadPatternGrid()
        Dim sql As String
        Dim TheView As DataView
        Dim TheDate As DateTime
        Dim r As Integer

        BPatternGrid.DataSource = Nothing

        If PeriodBox.Items.Count = 0 Then Exit Sub

        If BudgetsGrid.RowCount = 0 Then Exit Sub

        If AllocationLinesGrid.RowCount = 0 Then Exit Sub

        Try
            sql = "select distinct Month " & _
              "from patterns p, Pattern_Ids pids " & _
              "where pids.Pattern_ID = p.Pattern_ID " & _
              "and Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "' " & _
              "and pids.Period_ID = " & PeriodBox.SelectedValue & _
              " and pids.Allocation_ID = '" & AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'"
        Catch ex As Exception
            Exit Sub
            'period box is not ready yet
        End Try



        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            BPatternGrid.DataSource = Nothing
            Exit Sub
        End If


        sql = "select p.Pattern_ID as ID, ids.Pattern_Description as Description, Payment_ID, "
        For r = 0 To TheView.Count - 1
            TheDate = TheView(r).Item("Month")
            sql = sql + "sum(case Month when " & TheDate.ToOADate - 2 & " then cast(Percentage * 100 as decimal(9,6)) else 0 end) as [" & MonthName(TheDate.Month) & " " & TheDate.Year & "],"
            'sql = sql + "sum(case Month when " & TheDate.ToOADate - 2 & " then cast(Percentage * 100 as decimal(9,6)) else 0 end) as [" & MonthName(TheDate.Month) & " " & TheDate.Year & "],"
        Next

        'remove the last ,
        sql = Mid(sql, 1, Len(sql) - 1)

        sql = sql & " from Pattern_IDs ids, Patterns p where ids.Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & _
                    "' and ids.Allocation_ID = '" & AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & _
                    "' and ids.Period_ID = " & PeriodBox.SelectedValue.ToString & _
                    " and ids.Pattern_ID = p.Pattern_ID group by p.Pattern_ID, ids.Pattern_Description, Payment_ID"

        BPatternGrid.DataSource = LoadDataView(sql)

        BPatternGrid.Refresh()

        BPatternGrid.Columns(0).Frozen = True
        BPatternGrid.Columns(1).Frozen = True
        BPatternGrid.Columns(2).Visible = False


    End Sub

    Private Sub LoadBudgetsGrid()
        Job("Loading Budgets...")
        Dim sql As String

        sql = "Select Budget_ID as 'ID', Budget_Description as 'Description' from Budgets"

        BudgetsGrid.DataSource = LoadDataView(sql)
        BudgetsGrid.Refresh()

    End Sub

    Private Sub LoadPeriodBox()
        Job("Loading Periods...")
        Dim sql As String

        sql = "select * from Periods"

        PeriodBox.DataSource = LoadDataView(sql)
        PeriodBox.ValueMember = "Period_ID"
        PeriodBox.DisplayMember = "Period_Name"

        PeriodBox.SelectedValue = CurrentPeriodID

    End Sub

    Private Sub UpdateGrids()
        If BudgetTabs.SelectedTab.Name = "TabPage1" Then
            LoadAllocationLinesGrid()
            LoadPatternGrid()
        Else
            LoadPaymentsGrid()

        End If
    End Sub

    Private Sub LoadAllocationLinesGrid()
        Job("Loading Allocation Lines...")
        Dim sql As String

        If BudgetTabs.SelectedTab.Name <> "TabPage1" Then Exit Sub

        sql = "Select Allocation_ID as 'ID', Allocation_Description as 'Description', Allocation_Lines.Field_Code, Field_description as 'Field', Hidden from Allocation_Lines " & _
              "left outer join Fields_List_Part on Allocation_Lines.Field_Code = Fields_List_Part.Field_Code " & _
              "where Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "'"

        '  Clipboard.SetText(sql)

        AllocationLinesGrid.DataSource = LoadDataView(sql)
        AllocationLinesGrid.Refresh()

        AllocationLinesGrid.Columns("Field_Code").Visible = False

    End Sub

    Private Sub AddBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddBut.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to add budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Add Budgets", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Dim sql As String

        'clear boxes
        With BudgetsDialog
            .IDBox.Text = ""
            .DescriptionBox.Text = ""
            .IDBox.Enabled = True
        End With

        If BudgetsDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            sql = "insert into Budgets values('" & BudgetsDialog.IDBox.Text & "', '" & BudgetsDialog.DescriptionBox.Text & "')"

            If RunNonQuery(sql) = "" Then
                AddActivity(CurrentUser.Name, "Added a budget line called " & BudgetsDialog.DescriptionBox.Text, 13, "")
                LoadBudgetsGrid()
            End If
        End If
    End Sub

    Private Sub EditBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditBut.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Budget Edit", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets of this type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Budget Edit", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Dim sql As String
        Dim OldBudget As String

        If BudgetsGrid.SelectedRows.Count = 0 Then Exit Sub

        'fill boxes
        With BudgetsDialog
            .IDBox.Text = BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString
            .DescriptionBox.Text = BudgetsGrid.Item(1, BudgetsGrid.CurrentRow.Index).Value.ToString
            .IDBox.Enabled = False
            OldBudget = .DescriptionBox.Text
        End With

        If BudgetsDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            sql = "update Budgets set Budget_Description = '" & BudgetsDialog.DescriptionBox.Text & "' where Budget_ID = '" & BudgetsDialog.IDBox.Text & "'"

            If RunNonQuery(sql) = "" Then
                AddActivity(CurrentUser.Name, "Changed budget description from " & OldBudget & " to " & BudgetsDialog.DescriptionBox.Text, 14, "")
                LoadBudgetsGrid()
            End If
        End If
    End Sub

    Private Sub RefreshBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RefreshBut.Click
        LoadBudgetsGrid()

    End Sub

    Private Sub DeleteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBut.Click
        If BudgetsGrid.SelectedRows.Count = 0 Then Exit Sub

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to delete budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Budget Delete", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to delete budgets of this type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Budget Delete", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        If MessageBox.Show("Are you sure you want to delete " & _
                           BudgetsGrid.Item(1, BudgetsGrid.CurrentRow.Index).Value.ToString & "?", "Delete?", _
                           MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Dim sql As String

            sql = "delete Budgets where Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "'"

            If RunNonQuery(sql) = "" Then
                AddActivity(CurrentUser.Name, "Deleted budget line " & BudgetsGrid.Item(1, BudgetsGrid.CurrentRow.Index).Value.ToString, 15, "")
                LoadBudgetsGrid()

            End If
        End If
    End Sub

    Private Sub BudgetsGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles BudgetsGrid.CellEnter

        BudgetSelectedLabel.Text = BudgetsGrid.Item(1, BudgetsGrid.CurrentRow.Index).Value.ToString
        LoadAllocationsBox()

        UpdateGrids()

    End Sub


    Private Sub BudgetTabs_Selected(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TabControlEventArgs) Handles BudgetTabs.Selected
        UpdateGrids()

    End Sub


    Private Sub AllocationLineAddBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllocationLineAddBut.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Add Allocation Lines", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to add allocation lines for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Add Allocation Line", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Dim sql As String

        With AllocationLinesDialog
            .IDBox.Text = ""
            .IDBox.Enabled = True

            .DescriptionBox.Text = ""
            .LoadFieldBox()
            .FieldBox.SelectedIndex = 0
            .HiddenCheck.Checked = False
        End With

        If AllocationLinesDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim Hidden As Integer

            If AllocationLinesDialog.HiddenCheck.Checked Then
                Hidden = 1
            Else
                Hidden = 0
            End If

            sql = "insert into Allocation_Lines values ('" & _
                  AllocationLinesDialog.IDBox.Text & "', '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "', '" & _
                  AllocationLinesDialog.DescriptionBox.Text & "'," & AllocationLinesDialog.FieldBox.SelectedValue & ", " & Hidden.ToString & ")"
            If RunNonQuery(sql) = "" Then
                AddActivity(CurrentUser.Name, "Added an Allocation line called " & AllocationLinesDialog.DescriptionBox.Text, 16, "")
                UpdateGrids()
            End If
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Allocation Line Edit", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit allocation lines for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Allocation Line Edit", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Dim sql As String
        Dim OldDesc As String

        With AllocationLinesDialog
            .IDBox.Text = AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString
            .IDBox.Enabled = False

            .DescriptionBox.Text = AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString
            OldDesc = .DescriptionBox.Text
            .LoadFieldBox()
            .FieldBox.SelectedValue = AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Field_Code").Index, AllocationLinesGrid.CurrentRow.Index).Value
            If AllocationLinesGrid.Item(AllocationLinesGrid.Columns("Hidden").Index, AllocationLinesGrid.CurrentRow.Index).Value = 1 Then
                .HiddenCheck.Checked = True
            Else
                .HiddenCheck.Checked = False
            End If
        End With

        If AllocationLinesDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim Hidden As Integer
            If AllocationLinesDialog.HiddenCheck.Checked Then
                Hidden = 1
            Else
                Hidden = 0
            End If

            sql = "update Allocation_Lines set Allocation_Description = '" & AllocationLinesDialog.DescriptionBox.Text & "', Field_Code = " & AllocationLinesDialog.FieldBox.SelectedValue & ", Hidden = " & Hidden.ToString & _
                  " where Allocation_ID = '" & AllocationLinesDialog.IDBox.Text & " ' and Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "'"

            If RunNonQuery(sql) = "" Then
                AddActivity(CurrentUser.Name, "Edited Allocation line from " & OldDesc & " to " & AllocationLinesDialog.DescriptionBox.Text, 17, "")
                UpdateGrids()
            End If
        End If
    End Sub

    Private Sub AllocationLineDeleteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllocationLineDeleteBut.Click
        If AllocationLinesGrid.SelectedRows.Count = 0 Then Exit Sub

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to delete budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Allocation Line Delete", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to delete allocation lines for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Allocation Line Edit", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If


        If MessageBox.Show("Are you sure you want to delete " & _
                           AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "?", "Delete?", _
                           MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Dim sql As String

            sql = "delete Allocation_Lines where Budget_ID = '" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "' and Allocation_ID = '" & _
                   AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'"

            If RunNonQuery(sql) = "" Then
                AddActivity(CurrentUser.Name, "Deleted Allocation line " & AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString, 18, "")
                UpdateGrids()

            End If
        End If
    End Sub

    Private Sub AllocationLinesGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles AllocationLinesGrid.CellEnter
        LoadPatternGrid()
        LoadProviderPatternGrid()

    End Sub

    Private Sub PatternBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PatternBut.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Edit National Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit the national pattern for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Edit National Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        'set up the form

        'you cannot edit the national pattern once payments have started.  for this you need to go and add/edit provider patterns

        Dim sql1 As String
        Dim TheData As DataView

        sql1 = "select count(*) as Counter from payments where allocation_ID = '" & AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "' and Period_ID = " & PeriodBox.SelectedValue

        TheData = LoadDataView(sql1)

        If TheData(0).Item("Counter") <> 0 Then
            MessageBox.Show("Payments have already been made to the selected allocation line and year, you cannot make changes to the national pattern once payments are made", "Payments started", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        'load TypesBox

        With PatternDialog
            sql1 = "select * from payment_types where period_id = " & PeriodBox.SelectedValue
            .PaymentTypeBox.DataSource = LoadDataView(sql1)
            .PaymentTypeBox.DisplayMember = "Payment_Description"
            .PaymentTypeBox.ValueMember = "Payment_ID"

            If .PaymentTypeBox.Items.Count = 0 Then
                MessageBox.Show("No payment types exist for this period.  Please create payment types first.", "No payment types", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
        End With

        If BPatternGrid.RowCount <> 0 Then
            PatternDialog.IDBox.Enabled = False
            PatternDialog.IDBox.Text = BPatternGrid.Item(0, BPatternGrid.CurrentRow.Index).Value.ToString
            PatternDialog.DescriptionBox.Text = BPatternGrid.Item(1, BPatternGrid.CurrentRow.Index).Value.ToString
            PatternDialog.PaymentTypeBox.SelectedValue = BPatternGrid.Item(2, BPatternGrid.CurrentRow.Index).Value
            PatternDialog.LoadPattern(BPatternGrid.Item(0, BPatternGrid.CurrentRow.Index).Value.ToString)
            PatternDialog.PaymentTypeBox.Enabled = False

        Else
            PatternDialog.PaymentTypeBox.Enabled = True
            PatternDialog.IDBox.Text = ""
            PatternDialog.IDBox.Enabled = True
            PatternDialog.DescriptionBox.Text = ""
            PatternDialog.LoadPattern("")
        End If

        PatternDialog.Text = "National Pattern"
        PatternDialog.ProviderPanel.Visible = False
        PatternDialog.NationalPanel.Visible = True


        If PatternDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim sql As String
            Dim r As Integer
            Dim TheDate As DateTime
            Dim TheValue As Decimal


            With PatternDialog

                If .IDBox.Enabled = False Then
                    'First delete patterns
                    sql = "delete from Patterns where Pattern_ID = '" & .IDBox.Text & "'"
                    RunNonQuery(sql)

                    sql = "delete from Pattern_IDs where Pattern_ID = '" & .IDBox.Text & "'"
                    RunNonQuery(sql)
                End If

                For r = 0 To .PatternGrid.RowCount - 1
                    TheDate = .PatternGrid.Item(0, r).Value
                    TheValue = .PatternGrid.Item(1, r).Value / 100
                    sql = "insert into Patterns values('" & .IDBox.Text & "'," & TheDate.ToOADate - 2 & "," & TheValue.ToString & ")"
                    RunNonQuery(sql)
                Next

                'also insert ids
                sql = "insert into Pattern_IDs values ('" & .IDBox.Text & "','" & BudgetsGrid.Item(0, BudgetsGrid.CurrentRow.Index).Value.ToString & "'," & _
                      "'" & AllocationLinesGrid.Item(0, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'," & PeriodBox.SelectedValue & ",'" & .DescriptionBox.Text & "', " & .PaymentTypeBox.SelectedValue & ")"
                RunNonQuery(sql)
            End With

            AddActivity(CurrentUser.Name, "User edited or added the payment pattern for " & AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString, 19, "")
            LoadPatternGrid()
        End If
    End Sub


    Private Sub Provider_Edit_Pattern_But_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Provider_Edit_Pattern_But.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Edit Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit patterns for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Edit Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        If ProviderPatternGrid.SelectedRows.Count = 0 Then Exit Sub

        With PatternDialog
            .Text = "Provider Payment Pattern"
            .ProviderPanel.Visible = True
            .NationalPanel.Visible = False
            .NewProviderPattern = False
            .LoadRegionBox()
            .RegionBox.SelectedValue = ProviderPatternGrid.Item(ProviderPatternGrid.Columns("Region_Code").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString
            .LABox.SelectedValue = ProviderPatternGrid.Item(ProviderPatternGrid.Columns("LA_Code").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString
            .ProvidersBox.SelectedValue = ProviderPatternGrid.Item(ProviderPatternGrid.Columns("RID").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString
            .RegionBox.Enabled = False
            .ProvidersBox.Enabled = False
            .LABox.Enabled = False
            .SearchButton.Enabled = False
        End With



        If PatternDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim sql As String
            Dim r As Integer
            Dim TheDate As DateTime
            Dim TheValue As Decimal


            With PatternDialog

                'first delete it
                sql = "delete from provider_patterns " & _
                      " where Period_ID = " & PeriodBox.SelectedValue.ToString & _
                      " and Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
                      " and RID = '" & .ProvidersBox.SelectedValue.ToString & "'"
                RunNonQuery(sql)

                For r = 0 To .PatternGrid.RowCount - 1
                    TheDate = .PatternGrid.Item(0, r).Value
                    TheValue = .PatternGrid.Item(1, r).Value / 100

                    sql = "insert into Provider_Patterns values(" & PeriodBox.SelectedValue.ToString & ",'" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "','" & _
                          .ProvidersBox.SelectedValue.ToString & "'," & _
                          TheDate.ToOADate - 2 & "," & TheValue.ToString & ")"
                    RunNonQuery(sql)

                    UpdateActivePayments(PeriodBox.SelectedValue, .ProvidersBox.SelectedValue.ToString, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString)
                Next

            End With

            AddActivity(CurrentUser.Name, "User edited a provider payment pattern for (RID - " & PatternDialog.ProvidersBox.SelectedValue.ToString & ") " & AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString, 22, "")
            LoadProviderPatternGrid()
        End If

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Add Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to add patterns for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Add Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If


        If BPatternGrid.RowCount = 0 Then
            MessageBox.Show("First create a national pattern before attempting to create provider patterns", "No national pattern", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If BPatternGrid.RowCount <> 0 Then
            PatternDialog.IDBox.Text = ""
            PatternDialog.IDBox.Enabled = False

        Else
            PatternDialog.IDBox.Text = ""
            PatternDialog.IDBox.Enabled = True
        End If

        With PatternDialog

            .Text = "Provider Payment Pattern"
            .ProviderPanel.Visible = True
            .NationalPanel.Visible = False
            .NewProviderPattern = True
            .RegionBox.Enabled = True
            .ProvidersBox.Enabled = True
            .LABox.Enabled = True
            .SearchButton.Enabled = True

            .LoadRegionBox()
        End With

        If PatternDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim sql As String
            Dim r As Integer
            Dim TheDate As DateTime
            Dim TheValue As Decimal


            With PatternDialog

                For r = 0 To .PatternGrid.RowCount - 1
                    TheDate = .PatternGrid.Item(0, r).Value
                    TheValue = .PatternGrid.Item(1, r).Value / 100

                    sql = "insert into Provider_Patterns values(" & PeriodBox.SelectedValue.ToString & ",'" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "','" & _
                          .ProvidersBox.SelectedValue.ToString & "'," & _
                          TheDate.ToOADate - 2 & "," & TheValue.ToString & ")"
                    RunNonQuery(sql)

                    UpdateActivePayments(PeriodBox.SelectedValue, .ProvidersBox.SelectedValue.ToString, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString)
                Next

            End With

            AddActivity(CurrentUser.Name, "User added a provider payment pattern for (RID - " & PatternDialog.ProvidersBox.SelectedValue.ToString & ") " & AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString, 21, "")
            LoadProviderPatternGrid()
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Edit Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to delete patterns for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Delete Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        If MessageBox.Show("Are you sure want to remove the selected profile pattern?", "Delete profile pattern?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            Dim sql As String

            sql = "delete from provider_patterns " & _
                  " where Period_ID = " & PeriodBox.SelectedValue.ToString & _
                  " and Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
                  " and RID = '" & ProviderPatternGrid.Item(ProviderPatternGrid.Columns("RID").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString & "'"
            RunNonQuery(sql)

            UpdateActivePayments(PeriodBox.SelectedValue, ProviderPatternGrid.Item(ProviderPatternGrid.Columns("RID").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString, AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString)

            AddActivity(CurrentUser.Name, "User deleted a provider payment pattern for (RID - " & ProviderPatternGrid.Item(ProviderPatternGrid.Columns("RID").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString & ") " & AllocationLinesGrid.Item(1, AllocationLinesGrid.CurrentRow.Index).Value.ToString, 23, "")


            LoadProviderPatternGrid()

        End If
    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged
        LoadBudgetsGrid()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If ProviderPatternGrid.Rows.Count = 0 Then Exit Sub

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit budgets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Apply Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If BudgetTypeCheck(BudgetsGrid.Item(BudgetsGrid.Columns("ID").Index, BudgetsGrid.CurrentRow.Index).Value.ToString) <> "W" Then
            MessageBox.Show("Currently you do not have permission to edit patterns for this budget type." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Apply Provider Pattern", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If


        If MessageBox.Show("Are you sure you want to apply the selected pattern across all allocation lines for this provider?", "Apply across all allocation lines?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Me.Refresh()

            ShowLoadingForm()

            Dim sql As String
            Dim TheView As DataView
            Dim Pattern As DataView
            Dim rec As DataRowView
            Dim Prec As DataRowView
            Dim TheDate As DateTime

            sql = "select * from allocations where RID = '" & ProviderPatternGrid.Item(ProviderPatternGrid.Columns("RID").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString & "' " & _
                  "and Status = 'Approved' and Period_ID = " & PeriodBox.SelectedValue.ToString

            TheView = LoadDataView(sql)

            sql = "Select * from Provider_Patterns where " & _
                   " Period_ID = " & PeriodBox.SelectedValue.ToString & _
                   " and Allocation_ID = '" & AllocationLinesGrid.Item(AllocationLinesGrid.Columns("ID").Index, AllocationLinesGrid.CurrentRow.Index).Value.ToString & "'" & _
                   " and RID = '" & ProviderPatternGrid.Item(ProviderPatternGrid.Columns("RID").Index, ProviderPatternGrid.CurrentRow.Index).Value.ToString & "'"

            Pattern = LoadDataView(sql)

            For Each rec In TheView

                For Each Prec In Pattern
                    TheDate = Prec.Item("Month")

                    sql = "insert into Provider_Patterns values(" & PeriodBox.SelectedValue.ToString & ",'" & rec.Item("Allocation_ID").ToString & "','" & _
                             rec.Item("RID").ToString & "'," & _
                              TheDate.ToOADate - 2 & "," & Prec.Item("Percentage").ToString & ")"

                    RunNonQuery(sql)

                Next

                AddActivity(CurrentUser.Name, "User added a provider payment pattern for (RID - " & rec.Item("RID").ToString & ") " & rec.Item("Allocation_ID").ToString, 21, PeriodBox.SelectedValue.ToString)

            Next

            LoadProviderPatternGrid()
            HideLoadingForm()

            MessageBox.Show("The pattern has been applied across all lines", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)


        End If

    End Sub

    Private Sub RegionBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegionBox.SelectedIndexChanged
        LoadPaymentsGrid()

    End Sub

    Private Sub AllocationBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllocationBox.SelectedIndexChanged
        LoadPaymentsGrid()
    End Sub

    Private Sub PaymentGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles PaymentGrid.CellFormatting
        With PaymentGrid
            If .Columns(e.ColumnIndex).Name.Contains("20") Then
                .Columns(e.ColumnIndex).DefaultCellStyle.Format = "£#,#"
                e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            End If

            If .Item(.Columns("Change").Index, e.RowIndex).Value = 1 Then
                e.CellStyle.BackColor = Color.LightBlue

            End If

            If e.ColumnIndex = .Columns.Count - 1 Then
                .Columns(e.ColumnIndex).ReadOnly = True

            End If
        End With
    End Sub

    Private Sub DeletePaymentBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeletePaymentBut.Click
        If PeriodBox.SelectedValue <> 2010111 Then
            MessageBox.Show("You cannot make changes to the payments anymore.  If you need to make a change to a payment then please contact TheStore.EFA@education.gsi.gov.uk", "Changes cannot be made", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        Dim sql As String
        Dim Rid As String

        If MessageBox.Show("This will delete any payments attached and revert the payment profile back to the current percentage profile.  Are you sure you want to continue?", "Delete payments?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            With PaymentGrid
                Rid = .Item(.Columns("RID").Index, .CurrentCell.RowIndex).Value
                Try
                    sql = "delete from payments where period_ID = " & PeriodBox.SelectedValue & _
                    " and RID = '" & Rid & "'" & _
                    " and Allocation_ID = '" & AllocationBox.SelectedValue & "'"
                    RunNonQuery(sql)
                Catch ex As Exception

                End Try
                LoadPaymentsGrid()
                .CurrentCell = .Item(PaymentGrid.Columns("Provider").Index, RIDIndex(Rid))
                PaymentGrid.Focus()
            End With

        End If
    End Sub


    Private Sub PaymentGrid_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles PaymentGrid.DataError
        MessageBox.Show("This is not a valid value, please try again", "Invalid value", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Sub

    Private Sub PaymentGrid_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles PaymentGrid.CellValueChanged
        Dim c As Integer
        Dim TheValue As Double



        With PaymentGrid

            If e.ColumnIndex = .Columns.Count - 1 Then Exit Sub

            .Item(.Columns("Change").Index, e.RowIndex).Value = 1
            'calculate the last column to always match the total
            TheValue = 0
            For c = 8 To .Columns.Count - 2
                Try
                    TheValue = TheValue + .Item(c, e.RowIndex).Value
                Catch ex As Exception

                End Try
            Next
            .Item(.Columns.Count - 1, e.RowIndex).Value = .Item(.Columns("Allocation").Index, e.RowIndex).Value - TheValue
        End With
    End Sub

    Private Sub SavePayments()
        If PaymentGrid.RowCount = 0 Then Exit Sub

        If MessageBox.Show("This will save the changes made.  This can not be undone", "Save changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Dim r As Integer
            Dim c As Integer
            Dim sql As String

            For r = 0 To PaymentGrid.RowCount - 1
                With PaymentGrid
                    If .Item(.Columns("Change").Index, r).Value = 1 Then
                        'its a change to delete then save
                        sql = "delete from payments where period_ID = " & PeriodBox.SelectedValue & _
                            " and RID = '" & .Item(.Columns("RID").Index, r).Value & "'" & _
                            " and Allocation_ID = '" & AllocationBox.SelectedValue & "'"
                        RunNonQuery(sql)

                        For c = 8 To .Columns.Count - 1
                            Try
                                sql = "insert into Payments (Period_ID, RID, Allocation_ID, Payment_Date, Month, Month_Total) " & _
                                    "values (" & PeriodBox.SelectedValue & "," & _
                                    "'" & .Item(.Columns("RID").Index, r).Value & "'," & _
                                    "'" & AllocationBox.SelectedValue & "'," & _
                                    Now.ToOADate - 2 & "," & _
                                    "'1 " & .Columns(c).Name & "'," & _
                                    .Item(c, r).Value & ")"

                                RunNonQuery(sql)
                            Catch ex As Exception

                            End Try
                        Next

                    End If
                End With
            Next
            MessageBox.Show("The changes have been saved", "Changes saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub ClearChanges()
        Dim r As Integer
        If PaymentGrid.RowCount = 0 Then Exit Sub

        With PaymentGrid
            For r = 0 To .RowCount - 1
                .Item(.Columns("Change").Index, r).Value = 0
            Next
        End With
    End Sub

    Private Sub SaveChangesBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveChangesBut.Click

        If PeriodBox.SelectedValue <> 2010111 Then
            MessageBox.Show("You cannot make changes to the payments anymore.  If you need to make a change to a payment then please contact TheStore.EFA@education.gsi.gov.uk", "Changes cannot be made", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        SavePayments()
        'ClearChanges()
        Dim RID As String
        Try
            'this is just in case the grid has nothing selected.
            RID = PaymentGrid.Item(PaymentGrid.Columns("RID").Index, PaymentGrid.CurrentCell.RowIndex).Value
            LoadPaymentsGrid()
            PaymentGrid.CurrentCell = PaymentGrid.Item(PaymentGrid.Columns("Provider").Index, RIDIndex(RID))
            PaymentGrid.Focus()
        Catch ex As Exception
            LoadPaymentsGrid()
        End Try

    End Sub

    Private Sub SearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click
        Search_Form.WhoSentForMe = "BudgetsForm"
        If Search_Form.ShowDialog = Windows.Forms.DialogResult.OK Then
            With Search_Form
                'Dim i As Integer

                Dim RID As String = .SearchResults_DataViewGrid.Item(.SearchResults_DataViewGrid.Columns("RID").Index, .SearchResults_DataViewGrid.CurrentRow.Index).Value.ToString
                Dim Region As String = .SearchResults_DataViewGrid.Item(.SearchResults_DataViewGrid.Columns("Region_Code").Index, .SearchResults_DataViewGrid.CurrentRow.Index).Value.ToString


                If Region <> RegionBox.SelectedValue Then
                    If MessageBox.Show("This selection will cause any unsaved data to be lost, are you sure you want to continue?", "Continue?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
                        Exit Sub
                    End If
                End If

                RegionBox.SelectedValue = Region

                Try
                    PaymentGrid.CurrentCell = PaymentGrid.Item(PaymentGrid.Columns("Provider").Index, RIDIndex(RID))
                    PaymentGrid.Focus()
                Catch ex As Exception
                    MessageBox.Show("The provider could not be found with the current selection.  Try changing the alloction selection.", "Provider not found", MessageBoxButtons.OK, MessageBoxIcon.Information)

                End Try



            End With
        End If
    End Sub

    Private Function RIDIndex(ByVal RID As String) As Integer
        RIDIndex = -1
        For i = 0 To PaymentGrid.Rows.Count - 1
            If PaymentGrid.Item(PaymentGrid.Columns("RID").Index, i).Value.ToString = RID Then
                RIDIndex = i
                Exit Function
            End If
        Next
    End Function

    Private Sub BudgetsForm_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

        If UserFormCheck(Me.Name) = "N" Then
            Me.Close()
            MessageBox.Show("Currently you do not have permission to access this form." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk if you need access.", Me.Name & " Access", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

    End Sub

    Private Function BudgetTypeCheck(ByVal BudgetLine As String) As String

        'Returns N (No Access) R (Read Only) W (Write Access)

        Dim username As String = CurrentUser.Name
        Dim formid As String = Me.Name
        Dim provtype As String
        Dim sql As String
        Dim theview As DataView

        If CurrentUser.Level <> 5 Then

            If BudgetLine = "YPLRD" Or BudgetLine = "YPLRE" Then
                provtype = " = 'Acade'"
            Else : provtype = " <> 'Acade'"
            End If

            sql = "declare @provtype as nvarchar(50) declare @username as nvarchar(50) declare @formid as nvarchar(50) " & _
                    "set @username = '" & username & "' " & _
                    "set @formid = '" & formid & "' " & _
                    "select max(Access_Level) as Access_Level " & _
                    "from ( " & _
                    "	select max(Access_Level) as Access_Level " & _
                    "	from ( " & _
                    "			select gfa.Group_ID, ptg.Provider_Type_Code, gfa.Access_Level " & _
                    "			from Group_Form_Access gfa " & _
                    "			inner join dbo.Provider_Type_Groups ptg on gfa.Group_ID = ptg.Group_ID " & _
                    "			inner join dbo.Users_Groups ug on gfa.Group_ID = ug.Group_ID " & _
                    "			where ug.User_Name = @username and gfa.Form_ID = @formid " & _
                    "			and ptg.Provider_Type_Code" & provtype & _
                    "	) as a " & _
                    "	union " & _
                    "	select Default_Access_Level as Access_Level " & _
                    "	from dbo.Forms " & _
                    "	where Form_ID = @formid " & _
                    ") as b"

            theview = LoadDataView(sql)

            BudgetTypeCheck = theview.Item(0).Item("Access_Level").ToString

        Else : BudgetTypeCheck = "W"

        End If

    End Function


End Class
